package com.ai.imagetext.repository;

import com.ai.imagetext.entity.TextContent;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface TextContentRepository extends JpaRepository<TextContent, Long> {

    /**
     * 根据主键ID查找文案
     */
    @Query(value = "SELECT * FROM text_contents WHERE id = :id AND is_deleted = false", nativeQuery = true)
    Optional<TextContent> findByIdAndIsDeletedFalse(@Param("id") Long id);

    /**
     * 获取数据库最新一条文案
     */
    @Query(value = "SELECT * FROM text_contents ORDER BY created_at DESC LIMIT 1", nativeQuery = true)
    Optional<TextContent> findLatest();


    /**
     * 根据图片ID查找文案
     */
    List<TextContent> findByImageIdAndIsDeletedFalse(Long imageId);
    
    /**
     * 根据图片ID和文案类型查找文案
     */
    List<TextContent> findByImageIdAndContentTypeAndIsDeletedFalse(Long imageId, TextContent.ContentType contentType);
    
    /**
     * 根据图片ID和风格类型查找文案
     */
    List<TextContent> findByImageIdAndStyleTypeAndIsDeletedFalse(Long imageId, TextContent.StyleType styleType);
    
    /**
     * 根据图片ID、文案类型和风格类型查找文案
     */
    Optional<TextContent> findByImageIdAndContentTypeAndStyleTypeAndIsDeletedFalse(
            Long imageId, TextContent.ContentType contentType, TextContent.StyleType styleType);
    
    /**
     * 根据文案内容搜索
     */
    @Query("SELECT tc FROM TextContent tc WHERE tc.isDeleted = false AND tc.content LIKE %:keyword%")
    Page<TextContent> findByContentContaining(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据AI生成状态查找文案
     */
    List<TextContent> findByAiGenerationStatusAndIsDeletedFalse(TextContent.AIGenerationStatus status);
    
    /**
     * 统计指定AI生成状态且未删除的文案数量
     */
    long countByAiGenerationStatusAndIsDeletedFalse(TextContent.AIGenerationStatus status);
    
    /**
     * 根据文案类型统计数量
     */
    long countByContentTypeAndIsDeletedFalse(TextContent.ContentType contentType);
    
    /**
     * 根据风格类型统计数量
     */
    long countByStyleTypeAndIsDeletedFalse(TextContent.StyleType styleType);
    
    /**
     * 统计指定图片的文案数量
     */
    long countByImageIdAndIsDeletedFalse(Long imageId);
    
    /**
     * 软删除文案
     */
    @Modifying
    @Query("UPDATE TextContent tc SET tc.isDeleted = true, tc.updatedAt = CURRENT_TIMESTAMP WHERE tc.id = :id")
    void softDeleteById(@Param("id") Long id);
    
    /**
     * 根据图片ID软删除所有相关文案
     */
    @Modifying
    @Query("UPDATE TextContent tc SET tc.isDeleted = true, tc.updatedAt = CURRENT_TIMESTAMP WHERE tc.image.id = :imageId")
    void softDeleteByImageId(@Param("imageId") Long imageId);
    
    /**
     * 获取最近生成的文案
     */
    Page<TextContent> findByIsDeletedFalseOrderByCreatedAtDesc(Pageable pageable);
    
    /**
     * 根据文案类型和风格类型统计
     */
    @Query("SELECT tc.contentType, tc.styleType, COUNT(tc) FROM TextContent tc " +
           "WHERE tc.isDeleted = false GROUP BY tc.contentType, tc.styleType")
    List<Object[]> getContentStatistics();
    
    /**
     * 根据文案类型查找文案
     */
    List<TextContent> findByContentTypeAndIsDeletedFalse(TextContent.ContentType contentType);
    
    /**
     * 根据风格类型查找文案
     */
    List<TextContent> findByStyleTypeAndIsDeletedFalse(TextContent.StyleType styleType);
    
    /**
     * 根据文案类型和风格类型查找文案
     */
    List<TextContent> findByContentTypeAndStyleTypeAndIsDeletedFalse(
            TextContent.ContentType contentType, TextContent.StyleType styleType);
}